<?php

include_once('include/Config.php');

Class ReporteMetClarinDAL {

    private $link;

    public function __construct() {
        $this->conectar();
    }

    private function conectar() {
        $this->link = $GLOBALS["DBConnPDO"];
    }

    public function obtenerReporteArrayExcel($where) {
        $sql = "select s.FechaSolicitud,
                c.Nombre,
                c.apellido,
                td.DescBreve as 'TipoDoc',
                c.NumeroDocumento,
                c.Telefono,
                c.Telefono2,
                day(c.FechaNacimiento) as 'Dia',
                month(c.FechaNacimiento) as 'Mes',
                year(c.FechaNacimiento) as 'Anio',
                c.Email,
                '    ' as 'aux',
                '' as 'L1',
                '' as 'M1',
                '' as 'M11',
                '' as 'J1',
                '' as 'V1',
                '' as 'S1',
                '' as 'D1',                
                l.Descripcion as 'Localidad',
                p.Descripcion as 'Provincia',
                c.Calle,
                c.Numero,
                c.Piso,
                c.Depto,
                '' as 'EntreCalle1',
                '' as 'EntreCalle2',
                c.CodigoPostal,
                '' as 'ObservacionesDeLaEntrega',
                '' as 'Country',
                '' as 'DireccionInternaCountry',
                '' as 'Distribuidora',
                '' as 'Linea',
                '' as 'Vendedor',
                '' as 'Paquete',
                '' as 'ObservacionesParaElVendedorSugerido',
                tt.Descripcion as 'TipoTarjeta',
                mid(ct.NumeroTarjeta, 1, 1) as 'tj1',
                mid(ct.NumeroTarjeta, 2, 1) as 'tj2',
                mid(ct.NumeroTarjeta, 3, 1) as 'tj3',
                mid(ct.NumeroTarjeta, 4, 1) as 'tj4',
                mid(ct.NumeroTarjeta, 5, 1) as 'tj5',
                mid(ct.NumeroTarjeta, 6, 1) as 'tj6',
                mid(ct.NumeroTarjeta, 7, 1) as 'tj7',
                mid(ct.NumeroTarjeta, 8, 1) as 'tj8',
                mid(ct.NumeroTarjeta, 9, 1) as 'tj9',
                mid(ct.NumeroTarjeta, 10, 1) as 'tj10',
                mid(ct.NumeroTarjeta, 11, 1) as 'tj11',
                mid(ct.NumeroTarjeta, 12, 1) as 'tj12',
                mid(ct.NumeroTarjeta, 13, 1) as 'tj13',
                mid(ct.NumeroTarjeta, 14, 1) as 'tj14',
                mid(ct.NumeroTarjeta, 15, 1) as 'tj15',
                mid(ct.NumeroTarjeta, 16, 1) as 'tj16',
                '' as 'CantidadDeEjemplaresSolicitados',
                '' as 'L',
                '' as 'M',
                '' as 'M',
                '' as 'J',
                '' as 'V',
                '' as 'S',
                '' as 'D',
                '' as 'Dia1',
                '' as 'Mes1',
                '' as 'Anio1',
                '' as 'Nombre1',
                '' as 'Apellido1',
                '' as 'Tipo Doc',
                '' as 'Nro Doc',
                '' as 'Parentezco1',
                '' as 'Dia2',
                '' as 'Mes2',
                '' as 'Anio2',
                '' as 'Email2',
                '' as 'Nombre2',
                '' as 'Apellido2',
                '' as 'TipoDoc2',
                '' as 'NroDoc2',
                '' as 'Parentezco2',
                '' as 'Dia3',
                '' as 'Mes3',
                '' as 'Anio3',
                '' as 'Email3'
                from solicitud s
                inner join empresaproducto ep on ep.idProducto = s.idProducto and ep.idEmpresa = s.idEmpresa
                inner join cliente c on c.idCliente = s.idCliente
                inner join empresaprovincia p on p.idProvincia = c.idProvincia and p.idEmpresa = s.idEmpresa
                inner join empresalocalidad l on l.idLocalidad = c.idLocalidad and l.idProvincia = p.idProvincia and l.idEmpresa = s.idEmpresa
                inner join empresatipodocumento td on td.idTipoDocumento = c.idTipoDocumento and td.idEmpresa = s.idEmpresa
                inner join empresatipoactividad ta on ta.idTipoActividad = c.idTipoActividad and td.idEmpresa = s.idEmpresa
                inner join clientetarjeta ct on c.idCliente = ct.idCliente
                inner join empresatipotarjeta tt on tt.idTipoTarjeta = ct.idTipoTarjeta and tt.idEmpresa = s.idEmpresa ";

        $sql .= $where;

        $stmt = $this->link->prepare($sql);

        $stmt->execute();
        if ($stmt->errorCode() != 0) {
            $errors = $stmt->errorInfo();
        }
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $result;
    }

}

?>
